﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;

namespace EPE.DBUtility
{
    public class Pagination
    {
        /// <summary>
        /// 对Sql语句进行分页处理
        /// </summary>
        /// <param name="dataView">待处理的Sql语句</param>
        /// <param name="pageIndex">分页页码</param>
        /// <param name="dataCount">每页显示数据数量</param>
        /// <param name="sortExpression">排序规则</param>
        /// <param name="viewName">生成子视图名称</param>
        /// <param name="rowIndexName">生成行标记序列名称</param>
        /// <returns>处理后的分页语句</returns>
        public static String PagingDataView(String dataView, int pageIndex, int dataCount, String sortExpression, String viewName, String rowIndexName)
        {
            if (String.IsNullOrEmpty(dataView))
            {
                return dataView;
            }
            if (pageIndex < 1)
            {
                pageIndex = 1;
            }
            if (dataCount < 1)
            {
                dataCount = 1;
            }
            if (String.IsNullOrEmpty(viewName))
            {
                viewName = string.Format("View_{0}", Guid.NewGuid().ToString().Replace("-", string.Empty));
            }
            if (String.IsNullOrEmpty(rowIndexName))
            {
                rowIndexName = string.Format("RowIndex_{0}", Guid.NewGuid().ToString().Replace("-", string.Empty));
            }
            string rowFlag = string.Format("RowFlag_{0}", Guid.NewGuid().ToString().Replace("-", string.Empty));
            if (String.IsNullOrEmpty(sortExpression))
            {
                sortExpression = rowFlag;
            }

            string DataBaseType = ConfigurationSettings.AppSettings["DataBaseType"];

            string rtnSql = string.Empty;
            switch (DataBaseType)
            {
                case "SQLSERVER":
                    rtnSql= String.Format("select * from (select Row_Number() over(order by {0}) as {1},* from ({2}) as {3}_A) as {3}_B where {1} between {4} and {5} order by {0};select Count(*) as Amount from ({2}) as {3}_C ", sortExpression, rowIndexName, dataView, viewName, ((pageIndex - 1) * dataCount + 1), (pageIndex * dataCount));
                    break;
                case "OLEDB":
                    break;
                case "ORACLE":
                    break;
                case "POSTGRE":
                    break;
                case "MYSQL":
                    //limit初始行的偏移量为0（不是1）：
                    rtnSql = String.Format("{2} order by {0} limit {4} , {5};select Count(*) as Amount from ({2}) as {3}_C", sortExpression, rowIndexName, dataView, viewName, ((pageIndex - 1) * dataCount), dataCount-1);
                    break;
            }

            return rtnSql;

        }

        /// <summary>
        /// 对Sql语句进行分页处理
        /// </summary>
        /// <param name="dataView">待处理的Sql语句</param>
        /// <param name="pageIndex">分页页码</param>
        /// <param name="dataCount">每页显示数据数量</param>
        /// <param name="sortExpression">排序规则</param>
        /// <returns>处理后的分页语句</returns>
        public static String PagingDataView(String dataView, int pageIndex, int dataCount, String sortExpression)
        {
            return PagingDataView(dataView, pageIndex, dataCount, sortExpression, String.Empty, String.Empty);
        }

    }
}
